const mysql = require('mysql2/promise');
const fs = require('fs');
const path = require('path');

// 数据库配置
const dbConfig = {
    host: '43.143.253.188',
    port: 3306,
    user: 'nextjs',
    password: 'yEAiRE2jcxTyRBy5',
    database: 'nextjs',
    charset: 'utf8mb4',
    timezone: '+08:00'
};

async function initDeputyGeneralTables() {
    let connection;

    try {
        console.log('正在连接数据库...');
        connection = await mysql.createConnection(dbConfig);
        console.log('数据库连接成功！');

        // 读取SQL文件
        const sqlFilePath = path.join(__dirname, '../database/init-deputy-general-simple.sql');
        const sqlContent = fs.readFileSync(sqlFilePath, 'utf8');

        console.log('正在执行SQL脚本...');

        // 分割SQL语句并逐个执行
        const statements = sqlContent
            .split(';')
            .map(stmt => stmt.trim())
            .filter(stmt => stmt.length > 0 && !stmt.startsWith('--') && !stmt.startsWith('/*'));

        console.log(`准备执行 ${statements.length} 个SQL语句...`);

        for (let i = 0; i < statements.length; i++) {
            const statement = statements[i];
            if (statement.trim()) {
                try {
                    console.log(`执行语句 ${i + 1}/${statements.length}...`);
                    await connection.execute(statement);
                } catch (error) {
                    // 如果是表已存在的错误，继续执行
                    if (error.code === 'ER_TABLE_EXISTS_ERROR') {
                        console.log(`表已存在，跳过: ${error.message}`);
                        continue;
                    }
                    // 如果是重复键错误，继续执行
                    if (error.code === 'ER_DUP_ENTRY') {
                        console.log(`数据已存在，跳过: ${error.message}`);
                        continue;
                    }
                    // 如果是语法错误，显示更多信息
                    if (error.code === 'ER_PARSE_ERROR') {
                        console.error(`SQL语法错误: ${error.message}`);
                        console.error(`问题语句: ${statement.substring(0, 200)}...`);
                        continue;
                    }
                    console.error(`执行语句失败: ${statement.substring(0, 100)}...`);
                    console.error(`错误: ${error.message}`);
                    // 对于非关键错误，继续执行
                    if (error.code !== 'ER_NO_SUCH_TABLE') {
                        continue;
                    }
                }
            }
        }

        console.log('✅ 副将系统表初始化完成！');

        // 验证表是否创建成功
        console.log('\n正在验证表结构...');
        const tables = [
            'skill_base',
            'deputy_general',
            'player_deputy',
            'deputy_general_skill_learned',
            'deputy_general_equipment',
            'deputy_general_stone',
            'player_deputy_warehouse',
            'deputy_general_level_log',
            'deputy_general_star_log',
            'deputy_general_trans_log'
        ];

        for (const table of tables) {
            try {
                const [rows] = await connection.execute(`SHOW TABLES LIKE '${table}'`);
                if (rows.length > 0) {
                    console.log(`✅ 表 ${table} 创建成功`);
                } else {
                    console.log(`❌ 表 ${table} 创建失败`);
                }
            } catch (error) {
                console.log(`❌ 检查表 ${table} 时出错: ${error.message}`);
            }
        }

        // 检查技能数据
        try {
            const [rows] = await connection.execute('SELECT COUNT(*) as count FROM skill_base');
            console.log(`✅ 技能数据插入成功，共 ${rows[0].count} 条记录`);
        } catch (error) {
            console.log(`❌ 检查技能数据时出错: ${error.message}`);
        }

        // 检查视图
        try {
            const [views] = await connection.execute("SHOW TABLES LIKE 'v_%'");
            console.log(`✅ 视图创建成功，共 ${views.length} 个视图`);
        } catch (error) {
            console.log(`❌ 检查视图时出错: ${error.message}`);
        }

    } catch (error) {
        console.error('❌ 数据库初始化失败:', error.message);
        console.error('错误详情:', error);
        process.exit(1);
    } finally {
        if (connection) {
            await connection.end();
            console.log('数据库连接已关闭');
        }
    }
}

// 执行初始化
if (require.main === module) {
    initDeputyGeneralTables()
        .then(() => {
            console.log('\n🎉 副将系统数据库初始化完成！');
            console.log('现在可以启动应用程序了。');
            process.exit(0);
        })
        .catch((error) => {
            console.error('初始化失败:', error);
            process.exit(1);
        });
}

module.exports = { initDeputyGeneralTables };
